实践2 |
您所在的位置：网站首页 › python 链接oracle数据库 › 实践2 |
实践2
|
实践2-1 python连接数据库
java连接mysql数据库在:实践 1-1 JDBC使用详解。 这部分包含：python连接Oracle、以及window定时任务schtasks的一些简单设置。 1、python连接Oracle数据库 1.1 oracle连接环境配置注意：1.1部分的环境配置仅仅是其中一个我自己使用的方法，在1.2中会包含官网中使用代码指定oracle_client的方法。 一般来说，在你本地配置plsql访问oracle的环境，配置成功后，再运行python连接oracle数据库就没问题了。 在配置之前，ping一下oracle的ip，确定网络是互通的。然后安装好合适版本位数的plsql。 由于远端oracle并非自己配置的，访问的驱动、配置文件等也是别人给的，而且还有点乱七八糟，所以有关版本的问题个人也不是很清楚。 按照教程所说:"到oracle官网下载instantclient basic包，解压缩到E:\ProgramFile\instantclient。" 1.1.1 环境变量配置我是按图索骥，如果之前配置成功过，可以打开配置好的plsql(可以处于未登录状态)，查看帮助-支持信息，以及电脑中相关的环境变量在新环境中进行如下配置。 设置环境变量如下， Path=...;E:\ProgramFile\instantclient NLS_lANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK TNS_ADMIN= E:\ProgramFile\instantclient ORACLE_HOME= E:\ProgramFile\instantclient即： 将instantclient目录添加到Path， NLS_lANG配置则是防止中文乱码等情况的出现， TNS_ADMIN指向tnsnames.ora所在目录， ORACLE_HOME我就不怎么确定了，因为我的oci.dll和tnsnames.ora在两个目录中，plsql的安装目录又是另一个（我不想管了，能跑就行），我ORACLE_HOME指向的是oci.dll所在的目录，但是后面涉及的某个错误中描述的是"在环境变量中增加 ORACLE_HOME 指向64位安装目录，而不是 instantclient 目录" 1.1.2 PlSQL 配置中文版进入“工具”-“首选项”-oracle“连接”，将oci.dll所在目录及oci.dll文件路径填入下面红框框的位置，一般是 /* Oracle主目录 */ E:\ProgramFile\instantclient /* OCI库 */ E:\ProgramFile\instantclient\oci.dll
1.1.3 配置tnsnames.ora文件 这个文件需要配置，包含你连接数据库的ip、端口(1521)及服务名等信息。可配置多个连接，也可以只配置一个 # tnsnames.ora Network Configuration File: 文件目录\tnsnames.ora # Generated by Oracle configuration tools. 连接名1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 数据库ip1)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 数据库ip2)(PORT = 1521)) (LOAD_BALANCE = yes) (FAILOVER = ON) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = 服务名) ) ) 连接名2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 数据库ip)(PORT = 1521)) ) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = 服务名) )) 1.1.4 一些报错 一般来说是配置过程出错的原因，出错首先检查配置过程，过程没问题再考虑其他，仅仅记录一部分 登录界面连接为空：tnsnames.ora未正确配置，好像无法连接远端还会报错'tns....'之类的 登录之后界面正常，但是select的结果界面为空，无论访问任何表(甚至dual)都是这样子。初步怀疑是版本问题，所以后面我按照之前的方法将两个文件包都配置进去了。 Error while trying to retrieve text for error，如下 1.1.5 [报错] Error while trying to retrieve text for errorError while trying to retrieve text for error ORA-01019 的解决办法 这个问题出现在我未配置ORACLE_HOME的时候，说是plsql客户端和oracle服务之间32位与64位的冲突，给出的解决方法是“在环境变量中增加 ORACLE_HOME 指向64位安装目录，而不是 instantclient 目录。”以及最后根本的更换下载的instantclient basic包的版本。 我自己的解决方法（由于之前配置成功过，但是相关的文件包比较乱），将ORACLE_HOME指向合适版本的oci.dll所在目录。 1.2 cx_Oraclecx_Oracle的主页为:https://oracle.github.io/python-cx_Oracle/index.html 其开发文档为:https://cx-oracle.readthedocs.io/en/latest/index.html 1.2.1 安装与配置若是该命令无法安装，可下载相关安装包后手动配置安装，也可以添加代理。 pip install cx_Oracle python -m pip install cx_Oracle --proxy=http://proxy.example.com:80 --upgrade
在window上，提供了两种配置方法，一种如1.1部分一样，在自己本地配置好一个可用的客户端，配置好环境变量。还有一个是配置Oracle即时客户端。具体可看:https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html#installing-cx-oracle-on-windows 在 Windows 上，cx_Oracle 按如下方式查找 Oracle 客户端库： 在lib_dir调用中指定的目录中 cx_Oracle.init_oracle_client()。此目录应包含解压缩的 Instant Client 'Basic' 或 'Basic Light' 包中的库。如果您从完整客户端或数据库安装（例如 Oracle 数据库“XE”快捷版）传递库目录，则您需要事先设置环境以使用该软件安装，否则将找不到消息文件等文件. 在 Windows 上，当路径包含反斜杠时，请使用“原始”字符串，如lib_dir=r"C:\instantclient_19_6". 如果无法从 加载 Oracle 客户端库lib_dir，则会引发异常。 如果lib_dir未指定，则在安装 cx_Oracle 二进制模块的目录中查找 Oracle 客户端库。此目录应包含解压缩的 Instant Client 'Basic' 或 'Basic Light' 包中的库。如果未找到库，则不会引发异常并继续搜索，请参阅下一个要点。 在系统库搜索路径上的目录中，例如PATH 环境变量。如果无法加载 Oracle 客户端库，则会引发异常。 import cx_Oracle cx_Oracle.init_oracle_client(lib_dir=r"C:\oracle\instantclient_19_11")
如果要使用可选的Oracle配置文件，如tnsnames.ora,selnet.ora或oraacess.xml与即时客户端，将对应文件放在某个可访问的路径下，如c:\oracle\dir，然后在代码中指定。 import cx_Oracle cx_Oracle.init_oracle_client(lib_dir=r"C:\oracle\instantclient_19_11", config_dir=r"C:\oracle\your_config_dir")1.2.2 数据库连接 cx_Oracle进行数据库连接的方式有两种，独立连接与连接池。 独立连接由cx_Oracle.connect()或其别名创建cx_Oracle.Connection()，其参数如下所示 cx_Oracle.connect(user=None, password=None, dsn=None, mode=cx_Oracle.DEFAULT_AUTH, handle=0, pool=None, threaded=False, events=False, cclass=None, purity=cx_Oracle.ATTR_PURITY_DEFAULT, newpassword=None, encoding=None, nencoding=None, edition=None, appcontext=[], tag=None, matchanytag=None, shardingkey=[], supershardingkey=[])
比较常用的参数有user,password,dsn，其中dsn有以下几种形式： import cx_Oracle # 简单连接 dbconn = cx_Oracle.connect(user='myuser',password='mypsw',dns='127.0.0.1\sname') #使用默认端口1521 dbconn = cx_Oracle.connect(user='myuser',password='mypsw',dns='127.0.0.1：1521\sname') #指定端口 # 通过配置好的dns连接 # 方法1：通过封装的方法配置dhs dns=cx_Oracle.makedsn("127.0.0.1", 1521, service_name="sname") dbconn = cx_Oracle.connect(user='myuser',password='mypsw',dns=dns,encoding="UTF-8") # 方法2：直接配置 dsn = """(DESCRIPTION= (FAILOVER=on) (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=196.121.78.3)(PORT=1521))) (CONNECT_DATA=(SERVICE_NAME=sname)))""" dbconn = cx_Oracle.connect(user='myuser',password='mypsw',dns=dns,encoding="UTF-8") # 方法3：将dns的配置放在tnsnames.ora文件中，如上面1.1.3中配置的“连接1” dbconn = cx_Oracle.connect(user='myuser',password='mypsw',dns='连接1',encoding="UTF-8")
其他的参数如threaded在使用多线程访问数据库的时候（并没有设置池），有设置为true，其具体功能并未深究。 而数据库在使用完毕的时候需要关闭数据库，可以使用dbconn.close()，或者说更建议使用with语句 with cx_Oracle.connect(user=user, password=password, dsn="dbhost.example.com/orclpdb1", encoding="UTF-8") as connection: cursor = connection.cursor() cursor.execute("insert into SomeTable values (:1, :2)", (1, "Some string")) connection.commit()
当应用程序频繁连接与断开与数据库连接时，连接池对于性能很重要。池支持Oracle的高可用性特性，建议用于必须可靠的应用程序。池是cx_Oracle.SessionPool()在应用程序初始化时创建的。连接池具体的使用留到后面多线程访问的时候整理。 更加详细的可看:连接到Oracle数据库 1.2.3 数据库操作执行SQL语句是Python应用程序与Oracle数据库通信的主要方式。语句是使用方法Cursor.execute()或执行Cursor.executemany()。语句包括查询、数据库操作语音(DML)和数据定义语言(DLL)。也可以执行一些特殊语句。 SQL语句不应包含尾随分号;或正斜杠/。 1.2.3.1 SQL查询查询只能使用Cursor.execute()执行，可通过迭代行，或者可以使用Cursor.fetchone()、Cursor.fetchmany()或Cursor.fetchall()来获取数据。 其中光标Cursor由Connect.cursor()返回，用于执行sql语句。光标使用结束后需要确保关闭，可以使用close()方法或者with语句。 import cx_Oracle dbconn = cx_Oracle.connect(user='myuser',password='mypsw',dns='127.0.0.1：1521\sname') cur = dbconn.cursor() #创建光标 # 方法1 迭代行 for row in cur.execute('select * from my_table') print(row) # 方法2 fetchone cur.execute('select * from my_table') while True: rs = cur.fetchone() if rs==None: break print(rs) # 方法3 fetchmany--返回指定行数的数据 cur.execute('select * from my_table') row_nums=10 while True: rows = cur.fetchmany(row_nums) if not rows: break for r in rows: print(r) # 方法4 fetchall --返回全部数据 cur.execute('select * from my_table') rows = cur.fetchall() for r in rows: print(r) cur.close()#关闭光标
SQL查询还可以查询列名和数据类型。 with connection.cursor() as cur: cur.execute("select * from MyTable") for column in cur.description: print(column)1.2.3.2 可滚动光标 通过scrollable=True在创建光标时设置参数来创建可滚动光标。 cursor = connection.cursor(scrollable=True) cursor.execute("select * from ChildTable order by ChildId") cursor.scroll(mode="last") print("LAST ROW:", cursor.fetchone()) cursor.scroll(mode="first") print("FIRST ROW:", cursor.fetchone()) cursor.scroll(8, mode="absolute") print("ROW 8:", cursor.fetchone()) cursor.scroll(6) print("SKIP 6 ROWS:", cursor.fetchone()) cursor.scroll(-4) print("SKIP BACK 4 ROWS:", cursor.fetchone())1.2.3.3 限制行 查询数据通常分为一组或多组： 给出查询必须处理的行数的上限，这有助于提高数据库的可伸缩性。 执行“网页分页”，允许根据需要从一组行移动到下一组或上一组。 用于获取连续小集合中的所有数据以进行批处理，这个可通过调用fecthmany来处理 而其他的方法都要依托于SQL语句来实现。在文档中有提到过"Oracle Database 12c SQL 引入了一个OFFSET/FETCH子句，它类似于LIMITMySQL的关键字。"，只是这个我并没有使用过。 一般来说，使用的都是ROWNUM、MAX_NUM、MIN_NUM和ROW_NUMBER()来进行限制。 需要注意的一点在于,ROWNUM似乎是与查询结果绑定而不是表中结构绑定，譬如说，分页取结果，若是使用下面的方法，是错误的。 select * from my_table where rownum10 and rownum10 and rnum |
【本文地址】